/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.FichaVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class FichaDao extends DBase {

    Connection conn = null;

    public FichaDao() {
        super();
    }

    private void setEventoMed(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODEVENTMED_PK(rs.getInt("NUM_CODEVENTMED_PK"));
        bean.setTX_DESCRIPCION(rs.getString("TX_DESCRIPCION"));
    }

    private void setPaisMed(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODPAIS_PK(rs.getInt("NUM_CODPAIS_PK"));
        bean.setTX_DESCRIPCIONPAIS(rs.getString("TX_DESCRIPCION"));
    }

    private void setDiscMed(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDISCMEDALL_PK(rs.getInt("NUM_CODDISCMEDALL_PK"));
        bean.setTX_DESCRIPCIONDISC(rs.getString("TX_DESCRIPCION"));
    }

    private void setDiscEqu(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDISCEQU_PK(rs.getInt("NUM_CODDISCEQU_PK"));
        bean.setTX_DESCRIPCIONDISCEQU(rs.getString("TX_DESCRIPCION"));
    }

    private void setmaximo(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setMAXIMO(rs.getInt("maximo"));
    }

    private void setDetMed(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDETMEDALL_PK(rs.getInt("NUM_CODDETMEDALL_PK"));
        bean.setNUM_MEDALLAORO(rs.getInt("NUM_MEDALLAORO"));
        bean.setNUM_MEDALLAPLATA(rs.getInt("NUM_MEDALLAPLATA"));
        bean.setNUM_MEDALLABRONCE(rs.getInt("NUM_MEDALLABRONCE"));
        bean.setNUM_CODCABMEDALL_PK(rs.getInt("NUM_CODCABMEDALL_PK"));
        bean.setNUM_CODDISCMEDALL_PK(rs.getInt("NUM_CODDISCMEDALL_PK"));
    }

     private void setEventoMedallero(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODCABMEDALL_PK(rs.getInt("NUM_CODCABMEDALL_PK"));
        bean.setNUM_PERIODO(rs.getInt("NUM_PERIODO"));
        bean.setNUM_CODEVENTMED_PK(rs.getInt("NUM_CODEVENTMED_PK"));
        bean.setTX_DESCRIPCIONEVENTO(rs.getString("TX_DESCRIPCIONEVENTO"));
        bean.setNUM_CODPAIS_PK(rs.getInt("NUM_CODPAIS_PK"));
        bean.setTX_DESCRIPCIONPAIS(rs.getString("TX_DESCRIPCIONPAIS"));
        bean.setNUM_CODDETMEDALL_PK(rs.getInt("NUM_CODDETMEDALL_PK"));
        bean.setNUM_MEDALLAORO(rs.getInt("NUM_MEDALLAORO"));
        bean.setNUM_MEDALLAPLATA(rs.getInt("NUM_MEDALLAPLATA"));
        bean.setNUM_MEDALLABRONCE(rs.getInt("NUM_MEDALLABRONCE"));
        bean.setNUM_CODDISCMEDALL_PK(rs.getInt("NUM_CODDISCMEDALL_PK"));
    }

    private void setEvento(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODCABMEDALL_PK(rs.getInt("NUM_CODCABMEDALL_PK"));
        bean.setNUM_PERIODO(rs.getInt("NUM_PERIODO"));
        bean.setNUM_CODEVENTMED_PK(rs.getInt("NUM_CODEVENTMED_PK"));
        bean.setTX_DESCRIPCIONEVENTO(rs.getString("TX_DESCRIPCIONEVENTO"));
        bean.setNUM_CODPAIS_PK(rs.getInt("NUM_CODPAIS_PK"));
        bean.setTX_DESCRIPCIONPAIS(rs.getString("TX_DESCRIPCIONPAIS"));
    }

    private void setMedallero(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDETMEDALL_PK(rs.getInt("NUM_CODDETMEDALL_PK"));
        bean.setNUM_MEDALLAORO(rs.getInt("NUM_MEDALLAORO"));
        bean.setNUM_MEDALLAPLATA(rs.getInt("NUM_MEDALLAPLATA"));
        bean.setNUM_MEDALLABRONCE(rs.getInt("NUM_MEDALLABRONCE"));
        bean.setNUM_CODDISCMEDALL_PK(rs.getInt("NUM_CODDISCMEDALL_PK"));
        bean.setTX_DESCRIPCIONDISC(rs.getString("TX_DESCRIPCIONDISC"));

    }



    private void setTotales(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setSumaoro(rs.getInt("sumaoro"));
        bean.setSumaplata(rs.getInt("sumaplata"));
        bean.setSumabronce(rs.getInt("sumabronce"));
    }

    public void  setListRegion(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
          bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));

    }

    private void setSatisfaccion(FichaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODSATISF_PK(rs.getInt("NUM_CODSATISF_PK"));
        bean.setDT_FECHAENCUESTA(rs.getString("DT_FECHAENCUESTA"));
        bean.setTX_DESCEVENTO(rs.getString("TX_DESCEVENTO"));
        bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
        bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
        bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
        bean.setNUM_GENERO(rs.getInt("NUM_GENERO"));
        bean.setNUM_EDAD(rs.getInt("NUM_EDAD"));
        bean.setNUM_PART(rs.getInt("NUM_PART"));
        bean.setNUM_SATDOC(rs.getInt("NUM_SATDOC"));
        bean.setNUM_SATEQU(rs.getInt("NUM_SATEQU"));
        bean.setNUM_SATMAT(rs.getInt("NUM_SATMAT"));
        bean.setNUM_SATINFR(rs.getInt("NUM_SATINFR"));
        bean.setNUM_SATPUNT(rs.getInt("NUM_SATPUNT"));
        bean.setNUM_SATPROG(rs.getInt("NUM_SATPROG"));
        bean.setNUM_OPINION(rs.getInt("NUM_OPINION"));
        bean.setTX_COMENTARIO(rs.getString("TX_COMENTARIO"));
    }
    public void  setDetInf(FichaVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDETINFDEP_PK(rs.getInt("NUM_CODDETINFDEP_PK"));
          bean.setTX_NOMBRE(rs.getString("TX_NOMBRE"));
          bean.setTX_CERTIFESTADIO(rs.getString("TX_CERTIFESTADIO"));
          bean.setTX_RUTACERTIFESTADIO(rs.getString("TX_RUTACERTIFESTADIO"));
          bean.setTX_ADJUCERTIFESTADIO(rs.getString("TX_ADJUCERTIFESTADIO"));
          bean.setTX_CERTIFCOLISEO(rs.getString("TX_CERTIFCOLISEO"));
          bean.setTX_RUTACERTIFCOLISEO(rs.getString("TX_RUTACERTIFCOLISEO"));
          bean.setTX_ADJUCERTIFCOLISEO(rs.getString("TX_ADJUCERTIFCOLISEO"));
          bean.setTX_TIPOACCION(rs.getString("TX_TIPOACCION"));
          bean.setTX_ESTANDAR(rs.getString("TX_ESTANDAR"));
          bean.setTX_RUTAESTANDAR(rs.getString("TX_RUTAESTANDAR"));
          bean.setTX_ADJUESTANDAR(rs.getString("TX_ADJUESTANDAR"));
          bean.setTX_CERTIFAD(rs.getString("TX_CERTIFAD"));
          bean.setTX_RUTACERTIFAD(rs.getString("TX_RUTACERTIFAD"));
          bean.setNUM_CODINFDEP_PK(rs.getInt("NUM_CODINFDEP_PK"));


    }
    
    
    public void  setEqu(FichaVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODEQUIPAMIENTO_PK(rs.getInt("NUM_CODEQUIPAMIENTO_PK"));
          bean.setTX_TIPOMATERIAL(rs.getString("TX_TIPOMATERIAL"));
          bean.setNUM_MATERIAL(rs.getInt("NUM_MATERIAL"));
          bean.setNUM_CODDISCEQU_PK(rs.getInt("NUM_CODDISCEQU_PK"));
          bean.setTX_DESCRIPCIONDISCEQU(rs.getString("TX_DESCRIPCIONDISCEQU"));
    }



    public void  setInfDep(FichaVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODINFDEP_PK(rs.getInt("NUM_CODINFDEP_PK"));
          bean.setNUM_ANIO(rs.getInt("NUM_ANIO"));
          bean.setTX_TIPOINF(rs.getString("TX_TIPOINF"));
          bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
          bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
          bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
          bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));
          bean.setTX_NOMPRV(rs.getString("TX_NOMPRV"));
          bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));
    }


    public void  setInfAdm(FichaVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODINFADMIN_PK(rs.getInt("NUM_CODINFADMIN_PK"));
          bean.setNUM_ANIO(rs.getInt("NUM_ANIO"));
          bean.setTX_DIRECCION(rs.getString("TX_DIRECCION"));
          bean.setNUM_SEDE(rs.getInt("NUM_SEDE"));
          bean.setNUM_REMAC(rs.getInt("NUM_REMAC"));
          bean.setNUM_EQU(rs.getInt("NUM_EQU"));
          bean.setNUM_TIPOEQU1(rs.getInt("NUM_TIPOEQU1"));
          bean.setNUM_TIPOEQU2(rs.getInt("NUM_TIPOEQU2"));
          bean.setNUM_TIPOEQU3(rs.getInt("NUM_TIPOEQU3"));
          bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
          bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
          bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
          bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));
          bean.setTX_NOMPRV(rs.getString("TX_NOMPRV"));
          bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));
    }





    public List getRegion()  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql = "select * from me_programa";
            //////
                 sql = "SELECT  NUM_CODDPTO_PK,TX_NOMDPTO FROM ME_DEPARTAMENTO";

            ////
            rs = stmt.executeQuery(sql);
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setListRegion(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }

        return lista;
    }



    public ArrayList<FichaVO> selectEventoMed() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODEVENTMED_PK,TX_DESCRIPCION from ME_EVENTOMEDALL";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setEventoMed(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public ArrayList<FichaVO> selectPaisMed() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODPAIS_PK,TX_DESCRIPCION from ME_PAIS";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setPaisMed(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public ArrayList<FichaVO> selectListDisc() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDISCMEDALL_PK,TX_DESCRIPCION from ME_DISCIPLINAMED";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setDiscMed(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public ArrayList<FichaVO> selectListDiscFiltro(int id) throws SQLException {
        //PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql ="select * from componente where TX_DESCCOMPPYINV not in (select TX_DESCCOMPPYINV from ME_COMPPYINV where NUM_PYINV_PK="+codPrograma+")";
            sql = "select * from ME_DISCIPLINAMED where NUM_CODDISCMEDALL_PK not in (select NUM_CODDISCMEDALL_PK from ME_DETMEDALLERO where NUM_CODCABMEDALL_PK="+id+")";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setDiscMed(bean, rs);
                lista.add(bean);
            }
            //rs.close();
             //pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }






    public ArrayList<FichaVO> selectListDetMed(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_CODDETMEDALL_PK,NUM_MEDALLAORO,NUM_MEDALLAPLATA,NUM_MEDALLABRONCE,NUM_CODCABMEDALL_PK,NUM_CODDISCMEDALL_PK from ME_DETMEDALLERO where NUM_CODCABMEDALL_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setDetMed(bean, rs);
                lista.add(bean);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public ArrayList<FichaVO> selectListDetInf(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select * from ME_DETINFRAESDEPORT where NUM_CODINFDEP_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setDetInf(bean, rs);
                lista.add(bean);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public ArrayList<FichaVO> selectListEqu(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select equ.NUM_CODEQUIPAMIENTO_PK,equ.TX_TIPOMATERIAL,equ.NUM_MATERIAL,equ.NUM_CODDISCEQU_PK,discequ.TX_DESCRIPCION as TX_DESCRIPCIONDISCEQU  from ME_EQUIPAMIENTO equ inner join ME_DISCEQU discequ on discequ.NUM_CODDISCEQU_PK=equ.NUM_CODDISCEQU_PK where equ.NUM_CODDETINFDEP_PK=? order by NUM_CODDISCEQU_PK,NUM_CODEQUIPAMIENTO_PK";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setEqu(bean, rs);
                lista.add(bean);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }




    public int maxcabmed() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODCABMEDALL_PK) as maximo from ME_CABMEDALLERO";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setmaximo(bean, rs);
                max=bean.getMAXIMO();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }


    public int maxcabinf() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODINFDEP_PK) as maximo from ME_INFRAESDEPORT";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setmaximo(bean, rs);
                max=bean.getMAXIMO();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }



    public ArrayList<FichaVO> sumaTotales(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select isnull(sum(NUM_MEDALLAORO),0) as sumaoro,isnull(sum(NUM_MEDALLAPLATA),0) as sumaplata,isnull(sum(NUM_MEDALLABRONCE),0) as sumabronce from ME_DETMEDALLERO where NUM_CODCABMEDALL_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setTotales(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public ArrayList<FichaVO> selectListSatisfaccion() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select sat.NUM_CODSATISF_PK,convert(varchar,sat.DT_FECHAENCUESTA,103) as DT_FECHAENCUESTA   ,sat.TX_DESCEVENTO ,sat.NUM_GENERO ,sat.NUM_EDAD ,sat.NUM_PART,sat.NUM_SATDOC,sat.NUM_SATEQU,sat.NUM_SATMAT,sat.NUM_SATINFR,sat.NUM_SATPUNT,sat.NUM_SATPROG,sat.NUM_OPINION,sat.TX_COMENTARIO ,sat.NUM_CODDIST_PK,prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK from ME_SATISFACCION sat inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=sat.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setSatisfaccion(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public ArrayList<FichaVO> selectListEventoMedallero() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select cmed.NUM_CODCABMEDALL_PK,cmed.NUM_PERIODO,cmed.NUM_CODEVENTMED_PK,eventmed.TX_DESCRIPCION as TX_DESCRIPCIONEVENTO,cmed.NUM_CODPAIS_PK,pais.TX_DESCRIPCION as TX_DESCRIPCIONPAIS, dmed.NUM_CODDETMEDALL_PK , dmed.NUM_MEDALLAORO, dmed.NUM_MEDALLAORO, dmed.NUM_MEDALLAPLATA, dmed.NUM_MEDALLABRONCE, dmed.NUM_CODDISCMEDALL_PK  from dbo.ME_CABMEDALLERO cmed inner join dbo.ME_DETMEDALLERO dmed on dmed.NUM_CODCABMEDALL_PK=cmed.NUM_CODCABMEDALL_PK inner join ME_EVENTOMEDALL eventmed on eventmed.NUM_CODEVENTMED_PK=cmed.NUM_CODEVENTMED_PK inner join ME_PAIS pais on pais.NUM_CODPAIS_PK=cmed.NUM_CODPAIS_PK";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setEventoMedallero(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public ArrayList<FichaVO> selectListInfDep() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select infdep.NUM_CODINFDEP_PK,infdep.NUM_ANIO,infdep.TX_TIPOINF, dis.NUM_CODDIST_PK , prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK,dep.TX_NOMDPTO,prov.TX_NOMPRV,dis.TX_NOMDIST from ME_INFRAESDEPORT infdep inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=infdep.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setInfDep(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public ArrayList<FichaVO> selectListInfAdm() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select infadm.NUM_CODINFADMIN_PK,infadm.NUM_ANIO,infadm.TX_DIRECCION,infadm.NUM_SEDE,infadm.NUM_REMAC,infadm.NUM_EQU,infadm.NUM_TIPOEQU1,infadm.NUM_TIPOEQU2,infadm.NUM_TIPOEQU3, dis.NUM_CODDIST_PK , prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK,dep.TX_NOMDPTO,prov.TX_NOMPRV,dis.TX_NOMDIST from ME_INFRAESADMIN infadm inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=infadm.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setInfAdm(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public FichaVO selectListInfAdmId(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        FichaVO bean = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select infadm.NUM_CODINFADMIN_PK,infadm.NUM_ANIO,infadm.TX_DIRECCION,infadm.NUM_SEDE,infadm.NUM_REMAC,infadm.NUM_EQU,infadm.NUM_TIPOEQU1,infadm.NUM_TIPOEQU2,infadm.NUM_TIPOEQU3, dis.NUM_CODDIST_PK , prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK,dep.TX_NOMDPTO,prov.TX_NOMPRV,dis.TX_NOMDIST from ME_INFRAESADMIN infadm inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=infadm.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK where infadm.NUM_CODINFADMIN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new FichaVO();
            while (rs.next()) {
                setInfAdm(bean, rs);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return bean;
    }

    public FichaVO selectListInfDepId(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        FichaVO bean = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select infdep.NUM_CODINFDEP_PK,infdep.NUM_ANIO,infdep.TX_TIPOINF, dis.NUM_CODDIST_PK , prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK,dep.TX_NOMDPTO,prov.TX_NOMPRV,dis.TX_NOMDIST from ME_INFRAESDEPORT infdep inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=infdep.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK where infdep.NUM_CODINFDEP_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new FichaVO();
            while (rs.next()) {
                setInfDep(bean, rs);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return bean;
    }







    public FichaVO selectListSatisfaccionId(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        FichaVO bean = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select sat.NUM_CODSATISF_PK,convert(varchar,sat.DT_FECHAENCUESTA,103) as DT_FECHAENCUESTA   ,sat.TX_DESCEVENTO ,sat.NUM_GENERO ,sat.NUM_EDAD ,sat.NUM_PART,sat.NUM_SATDOC,sat.NUM_SATEQU,sat.NUM_SATMAT,sat.NUM_SATINFR,sat.NUM_SATPUNT,sat.NUM_SATPROG,sat.NUM_OPINION,sat.TX_COMENTARIO ,sat.NUM_CODDIST_PK,prov.NUM_CODPRV_PK,dep.NUM_CODDPTO_PK from ME_SATISFACCION sat inner join ME_DISTRITO dis on dis.NUM_CODDIST_PK=sat.NUM_CODDIST_PK inner join ME_PROVINCIA prov on prov.NUM_CODPRV_PK=dis.NUM_CODPRV_PK inner join ME_DEPARTAMENTO dep on dep.NUM_CODDPTO_PK=prov.NUM_CODDPTO_PK where sat.NUM_CODSATISF_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new FichaVO();
            while (rs.next()) {
                setSatisfaccion(bean, rs);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return bean;
    }

    public FichaVO selectListEventoId(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        FichaVO bean = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select cmed.NUM_CODCABMEDALL_PK,cmed.NUM_PERIODO,cmed.NUM_CODEVENTMED_PK,eventmed.TX_DESCRIPCION as TX_DESCRIPCIONEVENTO,cmed.NUM_CODPAIS_PK,pais.TX_DESCRIPCION as TX_DESCRIPCIONPAIS   from dbo.ME_CABMEDALLERO cmed  inner join ME_EVENTOMEDALL eventmed on eventmed.NUM_CODEVENTMED_PK=cmed.NUM_CODEVENTMED_PK inner join ME_PAIS pais on pais.NUM_CODPAIS_PK=cmed.NUM_CODPAIS_PK where cmed.NUM_CODCABMEDALL_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new FichaVO();
            while (rs.next()) {
                setEvento(bean, rs);
            }
            rs.close();
             pstmt.close();

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return bean;
    }


    public ArrayList<FichaVO> selectListDetalleId(int id) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        PreparedStatement pstmt = null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select  dmed.NUM_CODDETMEDALL_PK , dmed.NUM_MEDALLAORO, dmed.NUM_MEDALLAORO, dmed.NUM_MEDALLAPLATA, dmed.NUM_MEDALLABRONCE, dmed.NUM_CODDISCMEDALL_PK, discmed.TX_DESCRIPCION as TX_DESCRIPCIONDISC from dbo.ME_DETMEDALLERO dmed  inner join dbo.ME_CABMEDALLERO cmed on dmed.NUM_CODCABMEDALL_PK=cmed.NUM_CODCABMEDALL_PK inner join ME_DISCIPLINAMED discmed on discmed.NUM_CODDISCMEDALL_PK=dmed.NUM_CODDISCMEDALL_PK where cmed.NUM_CODCABMEDALL_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setMedallero(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }




    public ArrayList<FichaVO> selectListDiscEqu() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FichaVO> lista = new ArrayList<FichaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDISCEQU_PK,TX_DESCRIPCION from ME_DISCEQU";
            rs = stmt.executeQuery(sql);
            FichaVO bean = null;
            while (rs.next()) {
                bean = new FichaVO();
                setDiscEqu(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }








    public String spManCabMed(FichaVO bean) {
        String msj = "";
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_CABMED(?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODCABMEDALL_PK());
            cs.setInt(3, bean.getNUM_PERIODO());
            cs.setInt(4, bean.getNUM_CODEVENTMED_PK());
            cs.setInt(5, bean.getNUM_CODPAIS_PK());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


    public String spManInfr(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_INFR(?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODINFDEP_PK());
            cs.setInt(3, bean.getNUM_ANIO());
            cs.setString(4, bean.getNUM_CODDIST_PK());
            cs.setString(5, bean.getTX_TIPOINF());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public String spManDetMed(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            //ficha = new FichaVO(coddetmedall, numoro,numplata,numbronce,max,coddisc,tipoOper);
            CallableStatement cs = conn.prepareCall("{call Man_DETMED(?,?,?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODDETMEDALL_PK());
            cs.setInt(3, bean.getNUM_MEDALLAORO());
            cs.setInt(4, bean.getNUM_MEDALLAPLATA());
            cs.setInt(5, bean.getNUM_MEDALLABRONCE());
            cs.setInt(6, bean.getNUM_CODCABMEDALL_PK());
            cs.setInt(7, bean.getNUM_CODDISCMEDALL_PK());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


    public String spManDetInfraes(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_DETINFR(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODDETINFDEP_PK());
            cs.setInt(3, bean.getNUM_CODINFDEP_PK());
            cs.setString(4, bean.getTX_NOMBRE());
            cs.setString(5, bean.getTX_CERTIFESTADIO());
            cs.setString(6, bean.getTX_RUTACERTIFESTADIO());
            cs.setString(7, bean.getTX_ADJUCERTIFESTADIO());
            cs.setString(8, bean.getTX_CERTIFCOLISEO());
            cs.setString(9, bean.getTX_RUTACERTIFCOLISEO());
            cs.setString(10, bean.getTX_ADJUCERTIFCOLISEO());
            cs.setString(11, bean.getTX_TIPOACCION());
            cs.setString(12, bean.getTX_ESTANDAR());
            cs.setString(13, bean.getTX_RUTAESTANDAR());
            cs.setString(14, bean.getTX_ADJUESTANDAR());
            cs.setString(15, bean.getTX_CERTIFAD());
            cs.setString(16, bean.getTX_RUTACERTIFAD());
            cs.setString(17, bean.getTX_ADJUCERTIFAD());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public String spManEqu(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_EQU(?,?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODEQUIPAMIENTO_PK());
            cs.setString(3, bean.getTX_TIPOMATERIAL());
            cs.setInt(4, bean.getNUM_MATERIAL());
            cs.setInt(5, bean.getNUM_CODDISCEQU_PK());
            cs.setInt(6, bean.getNUM_CODDETINFDEP_PK());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }



    public String spManSatisfaccion(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            CallableStatement cs = conn.prepareCall("{call Man_SATISFACCION(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODSATISF_PK());
            cs.setString(3, bean.getDT_FECHAENCUESTA());
            cs.setString(4, bean.getTX_DESCEVENTO());
            cs.setString(5, bean.getNUM_CODDIST_PK());
            cs.setInt(6, bean.getNUM_GENERO());
            cs.setInt(7, bean.getNUM_EDAD());
            cs.setInt(8, bean.getNUM_PART());
            cs.setInt(9, bean.getNUM_SATDOC());
            cs.setInt(10, bean.getNUM_SATEQU());
            cs.setInt(11, bean.getNUM_SATMAT());
            cs.setInt(12, bean.getNUM_SATINFR());
            cs.setInt(13, bean.getNUM_SATPUNT());
            cs.setInt(14, bean.getNUM_SATPROG());
            cs.setInt(15, bean.getNUM_OPINION());
            cs.setString(16, bean.getTX_COMENTARIO());

            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }



    public String spManInfAdm(FichaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            CallableStatement cs = conn.prepareCall("{call Man_INFRADM(?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, bean.getTIPOOPERACION());
            cs.setInt(2, bean.getNUM_CODINFADMIN_PK());
            cs.setInt(3, bean.getNUM_ANIO());
            cs.setString(4, bean.getTX_DIRECCION());
            cs.setInt(5, bean.getNUM_SEDE());
            cs.setInt(6, bean.getNUM_REMAC());
            cs.setInt(7, bean.getNUM_EQU());
            cs.setInt(8, bean.getNUM_TIPOEQU1());
            cs.setInt(9, bean.getNUM_TIPOEQU2());
            cs.setInt(10, bean.getNUM_TIPOEQU3());
            cs.setString(11, bean.getNUM_CODDIST_PK());
            

            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }




}
